package co.th.bjc.md.helper;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import co.th.bjc.md.common.ConnectDB;
import co.th.bjc.md.model.Province;

public class ProvinceHelper {
	
	private static Logger _log = Logger.getLogger(ProvinceHelper.class);
	
	public List<Province> findAllProvince() throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Province> listProvince = new ArrayList<Province>(0);
		ConnectDB connectDB = null;
		try{
			String sql = "SELECT * FROM province";
			_log.info("SQL : " + sql);
			
			connectDB = new ConnectDB();
			conn = connectDB.getConnection();
			stmt = conn.prepareStatement(sql);
			
			rs = stmt.executeQuery();
			while (rs.next()){
				listProvince.add(this.setProvince(rs));
			}
		}catch(Exception ex){
			_log.error("findAllProvince --> " + ex.toString());
			ex.printStackTrace();
		}finally{
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			connectDB.close();
		}
		return listProvince;
	}
	
	public Province findProvinceByProvinceCode(BigDecimal provinceCode) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Province province = null;
		ConnectDB connectDB = null;
		try{
			String sql = "SELECT * FROM province WHERE province_id = ?";
			_log.info("SQL : " + sql);
			
			connectDB = new ConnectDB();
			conn = connectDB.getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setBigDecimal(1, provinceCode);
			
			rs = stmt.executeQuery();
			if (rs.next()){
				province = this.setProvince(rs);
			}
		}catch(Exception ex){
			_log.error("findProvinceByProvinceCode --> " + ex.toString());
			ex.printStackTrace();
		}finally{
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			connectDB.close();
		}
		return province;
	}
	
	public List<Province> findProvinceByRegion(BigDecimal region) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Province> listProvince = new ArrayList<Province>(0);
		ConnectDB connectDB = null;
		try{
			String sql = "SELECT * FROM province WHERE region_fk = ?";
			_log.info("SQL : " + sql);
			
			connectDB = new ConnectDB();
			conn = connectDB.getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setBigDecimal(1, region);
			
			rs = stmt.executeQuery();
			while (rs.next()){
				listProvince.add(this.setProvince(rs));
			}
		}catch(Exception ex){
			_log.error("findProvinceByRegion --> " + ex.toString());
			ex.printStackTrace();
		}finally{
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			connectDB.close();
		}
		return listProvince;
	}
	
	private Province setProvince(ResultSet rs) throws Exception{
		Province province = new Province();
		province.setProvinceId(rs.getBigDecimal("province_id"));
		province.setProvinceNameTH(rs.getString("province_name_th"));
		province.setProvinceNameEn(rs.getString("province_name_en"));
		return province;
	}
}
